#include "sqldialog.h"
#include "ui_sqldialog.h"
#include <QDebug>
//#include <QLibrary>
sqlDialog::sqlDialog(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::sqlDialog)
{
    ui->setupUi(this);
    //连接数据库
    createDB();
    //创建表
    createTable();
    //连接插入按钮和槽函数
    connect(ui->insertButton, SIGNAL(clicked()), this, SLOT(insertItem()));
    //连接删除
    connect(ui->deleteButton, SIGNAL(clicked()), this, SLOT(deleteItem()));
    //连接修改
    connect(ui->modifyButton, SIGNAL(clicked()), this, SLOT(modifyItem()));
    //连接排序
    connect(ui->sortButton, SIGNAL(clicked()), this, SLOT(sortTable()));

    queryTable();

}

sqlDialog::~sqlDialog()
{
    delete ui;
}

bool sqlDialog::createDB()
{
    /*
    //建立与数据库的连接,参数指定使用哪一个数据库驱动
    //支持QMYSQL、QPSQL..
    //判断默认的链接是否存在
    */
    if(QSqlDatabase::contains(db.connectionName())){
        db.QSqlDatabase::database(db.connectionName());

    }
    else{
         db = QSqlDatabase::addDatabase("QSQLITE");
         qDebug(db.connectionName().toStdString().c_str());
    }


    //设置数据库的名字
    db.setDatabaseName("studentDB.db");
    //建立和数据库的连接之前必须打开数据库
    if(!db.open())
    {
        QMessageBox::critical(0, "Data Error", db.lastError().text());
        return false;
    }
    else{
        //开启事务处理
        db.transaction();
    }
    return true;
}
//创建表
bool sqlDialog::createTable()
{
    //QSqlQuery类提供了SQL语句执行和操作类
    QSqlQuery query;
    //创建表语句
    QString createTabStr = QString("CREATE TABLE Students ( \
                            id INTEGER PRIMARY KEY, \
                            name VARCHAR(20) NOT NULL, \
                            score REAL NOT NULL)");
    if(!query.exec(createTabStr))
    {
            return false;

    }
    else{
            db.commit();
            qDebug("Success!");
            return true;
    }


}
//插入数据
bool sqlDialog::insertItem(){
    QSqlQuery query;
    int num = ui->numEdit->text().toInt();
    QString name(ui->nameEdit->text().toStdString().c_str());
    double score = ui->scoreEdit->text().toDouble();

    //拼字符串
    query.prepare("INSERT INTO Students (id, name, score) VALUES(:id,:name,:score)");
    query.bindValue(":id", num);
    query.bindValue(":name", name);
    query.bindValue(":score", score);
    qDebug() << QString("insert into Students values(%1, %2, %3)").arg(num).arg(name).arg(score);
    if(!query.exec())
    {
        //qDebug() << "insert into error";
        QMessageBox::critical(this, "Insert Error","insert into Error",QMessageBox::Ok);
        return false;
    }
    db.commit();
    //查询并显示
    queryTable();
    return true;
}
//删除
bool sqlDialog::deleteItem()
{
    QSqlQuery query;
    QString SQL,num,name,score;

    num = ui->numEdit->text();
    name = ui->nameEdit->text();
    score = ui->scoreEdit->text();
    if(!num.isEmpty())
    {
        SQL = QString(" AND id = %1").arg(num.toInt());
    }
    if(!name.isEmpty())
    {
        SQL += QString(" AND name = \"%1\"").arg(name.toStdString().c_str());
    }
    if(!score.isEmpty())
    {
        SQL += QString(" AND score = %1").arg(score.toDouble());
    }

    QString delSql = QString("delete from Students where 1=1 %1").arg(SQL);
    qDebug() << delSql;
    SQL.clear();
    if(!query.exec(delSql))
    {

        QMessageBox::critical(this, "ERROR","DELETE FAILED", QMessageBox::Ok);
        return false;

    }
    //查询并显示
    queryTable();
    return true;
}
//修改
bool sqlDialog::modifyItem()
{
    QSqlQuery query;
    QString SQL =QString("SET "),
            num = ui->numEdit->text(),
            name = ui->nameEdit->text(),
            score = ui->scoreEdit->text();
    if(!num.isEmpty())
        SQL += QString("id = %1, ").arg(num.toInt());
    if(!name.isEmpty())
        SQL += QString("name = \"%1\", ").arg(name);
    if(!score.isEmpty())
        SQL += QString("score = %1 ").arg(score.toDouble());
    if(num.isEmpty() || name.isEmpty() && score.isEmpty())
    {
        QMessageBox::critical(this,"Modify Error",QObject::tr("请输入正确的格式"));
        return false;
    }
    SQL =SQL.trimmed();
    if(SQL.endsWith(","))
    {

        SQL =SQL.mid(0,SQL.length()-1);
    }
    QString modSql = QString("update Students %1 where id = %2 ").arg(SQL).arg(num.toInt());
    qDebug()<< modSql;
    SQL.clear();
    if(!query.exec(modSql))
    {
        QMessageBox::critical(this,"Modify Error",QObject::tr("执行错误"));
        return false;

    }
    query.exec("commit");
    //查询并显示
    queryTable();
    return true;
}

void sqlDialog::queryTable()
{
    QString str = QString("select * from Students order by id");
    QSqlQueryModel *model = new QSqlQueryModel;
    model->setQuery(str);
    ui->menuTableView->setModel(model);
}
void sqlDialog::sortTable()
{
    QString value = ui->valueComboBox->currentText();
    QString condition("asc");
    qDebug() << value << condition << ui->condComboBox->currentIndex();
    if(ui->condComboBox->currentIndex())
    {
        condition = "desc";
        qDebug()<< condition;
    }
    else
    {
        condition ="asc";
        qDebug()<< condition;
    }
    QString Sql = QString("select * from Students order by %1 %2").arg(value).arg(condition);
    qDebug()<< Sql;
    QSqlQueryModel *model = new QSqlQueryModel;
    model->setQuery(Sql);
    if (model->lastError().isValid())
        qDebug() << model->lastError();
    ui->menuTableView->setModel(model);
}

